package com.service;

import com.database.DatabaseConnection;
import com.model.Group;
import com.model.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentService {
    private DatabaseConnection dbConnection;

    public StudentService(DatabaseConnection dbConnection) {
        this.dbConnection = dbConnection;
    }

    // 添加学生
    public void addStudent(int id, String studentName, int groupId) {
        String sql = "INSERT INTO Students (Id, name, group_id) VALUES (?, ?, ?)";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, id);
            statement.setString(2, studentName);
            statement.setInt(3, groupId);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据姓名获取学生列表
    public List<Student> getStudentListByName(String name, String currentClass) {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM Students WHERE name LIKE ?";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, "%" + name + "%");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String studentName = resultSet.getString("name");
                students.add(new Student(studentName, id));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }

    // 获取所有学生列表

    public List<Student> getAllStudents(String currentClass) {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM Students";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                students.add(new Student(name, id));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }

    // 更新学生信息
    public void updateStudent(int studentId, String newName) {
        String sql = "UPDATE Students SET name = ? WHERE id = ?";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, newName);
            statement.setInt(2, studentId);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除学生
    public void deleteStudent(int studentId) {
        String sql = "DELETE FROM Students WHERE Id = ?";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, studentId);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public List<Group> getAllGroupsForClass(String className) {
        List<Group> groups = new ArrayList<>();
        String sql = "SELECT * FROM Groups WHERE class_id = (SELECT id FROM Classes WHERE name = ?)";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, className);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                groups.add(new Group(id, name));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return groups;
    }

    // 获取特定班级的所有学生
    public List<String> getAllStudentsForClass(String currentClass) {
        List<String> students = new ArrayList<>();
        String sql = "SELECT s.name FROM students s " +
                     "JOIN Groups g ON s.group_id = g.id " +
                     "WHERE g.class_id = ?"; // 查询当前班级的所有小组的学生
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, currentClass); // 设置班级名称
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    students.add(resultSet.getString("name")); // 获取学生姓名
                }
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 处理异常
        }
        return students; // 返回学生姓名列表
    }
}